package com.scalar.chiptrack.adaptor.akrossilicon.spel;

import com.scalar.chiptrack.jobs.JobHandler;
import com.scalar.chiptrack.adaptor.AdaptorConstants;
import com.scalar.chiptrack.adaptor.AdaptorUtils;
import com.scalar.chiptrack.adaptor.TestLot;
import com.scalar.chiptrack.adaptor.AssemblyLot;
import com.scalar.chiptrack.adaptor.InventoryLot;
import com.scalar.chiptrack.adaptor.utils.ParserResult;
import com.scalar.chiptrack.adaptor.utils.Parser;
import com.scalar.chiptrack.adaptor.utils.ParserFactory;
import com.scalar.chiptrack.adaptor.utils.XLSParserImpl;
import com.scalar.chiptrack.adaptor.dao.*;
import com.scalar.chiptrack.utils.Logger;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.chiptrack.operations.process.dao.NewProductDAO;
import com.scalar.chiptrack.operations.process.ParameterTypes;
import com.scalar.ScalarException;

import java.util.HashMap;
import java.util.ArrayList;
import java.io.File;

/**
 * Created by IntelliJ IDEA.
 * User: munugoti
 * Date: 15 Feb, 2010
 * Time: 11:28:18 AM
 */
public class SPELDataParser extends JobHandler implements AdaptorConstants
{
    private HashMap<String, AssemblyLot>  m_assemblyDataMap = new HashMap<String, AssemblyLot>();
    private HashMap<String, InventoryLot> m_dieBankDataMap  = new HashMap<String, InventoryLot>();
    private HashMap<String, InventoryLot> m_partialWaferMap  = new HashMap<String, InventoryLot>();
    private HashMap<String, TestLot> m_testDataMap          = new HashMap<String, TestLot>();
    private HashMap<String, InventoryLot> m_trDataMap	    = new HashMap<String, InventoryLot>();
    private HashMap<String, InventoryLot> m_fgDataMap	    = new HashMap<String, InventoryLot>();
    private Logger m_parserLogger   = null;
    private String m_dataOutputDir  = null;

    public SPELDataParser() throws Exception
    {
        try
        {
            m_dataOutputDir = AdaptorUtils.getDataOutputDir(VENDOR_NAME_SPEL, WIP_TYPE_ASSEMBLY );
            AdaptorUtils.makeAllAdaptorDirs(m_dataOutputDir);
            m_parserLogger = AdaptorUtils.getDataParserLogger(m_dataOutputDir,VENDOR_NAME_SPEL);
            TestAdaptorDAO.setLogger(m_parserLogger);
            AssemblyAdaptorDAO.setLogger( m_parserLogger);
            InventoryLotDAO.setLogger( m_parserLogger );
            ShippedLotDAO.setLogger( m_parserLogger );
            AdaptorUtils.setLogger( m_parserLogger);
        }
        catch (ScalarException e)
        {
            log("ERROR!! ERROR!! ERROR!! Exception while loading SPEL output directory details from database, Exception: " + e);
            throw e;
        }
    }
    /**
     *
     * @param args
     * @throws Exception
     */
    public static void main(String args[]) throws Exception
    {
        new SPELDataParser().execute();
    }

    /**
     *
     * @throws Exception
     */
    public void execute() throws Exception
    {
        AdaptorUtils.printHeader(VENDOR_NAME_SPEL, CONSTANT_PARSER);

        File doneDestDir = new File(m_dataOutputDir + "/done");
        File wipDataoutDir = new File(m_dataOutputDir);

        File[] fileList = wipDataoutDir.listFiles();

        if ((fileList == null) || (fileList.length <= 3))
        {
            log("No file found in directory: " + wipDataoutDir);
            throw new ScalarException("No file found in directory: " + wipDataoutDir + "----\n\n");
        }

        ArrayList<File> successFileList = new ArrayList<File>();
        boolean success = false;

        //PARSING STARTS FROM HERE
        for (int i = 0; i < fileList.length; i++)
        {
            if (fileList[i].isDirectory() == true)
            {
                continue;
            }

            if ((fileList[i].getName() != null) && ( fileList[i].getName().trim().startsWith(SPELPOP3Adaptor.FILE_NAME_WIP) ) )
            {
                try
                {
                    parseWipReport( fileList[i].getAbsolutePath() );

                    successFileList.add(fileList[i]);
                    success = true;
                }
                catch( Exception e )
                {
                    success = false;
                    log("Exception while parsing file: " + fileList[i].getAbsolutePath() + "\nException: " + e);
                    e.printStackTrace();
                    throw e;
                }
            }
        }

        /*Updating the Die Bank Lot Information*/
        try
        {
            // Save Die Bank Information
            if (m_dieBankDataMap.isEmpty() == false)
            {
                log(" Updating Die Bank Lot Information");
                InventoryLotDAO.saveInventoryLot(null, m_dieBankDataMap.values().iterator(), LOCATION_TYPE_DIE_BANK, VENDOR_NAME_SPEL);
            }
            else
            {
                InventoryLotDAO.closeInventoryLot(null, LOCATION_TYPE_DIE_BANK, VENDOR_NAME_SPEL);
            }
            success = true;
            m_dieBankDataMap.clear();
            m_dieBankDataMap = null;
        }
        catch (ScalarException se)
        {
            success = false;
            se.printStackTrace();
            log("ERROR!! ERROR!! ERROR!! Could not update Die Bank Lot information into database: " + se);
            throw se;
        }
        if ( !m_partialWaferMap.isEmpty() )
        {
            InventoryLotDAO.saveUpdatePartialBinInfo(null, m_partialWaferMap.values().iterator(), LOCATION_TYPE_DIE_BANK, VENDOR_NAME_SPEL );
        }
        /*Updating the Assembly Lot Information*/
        try
        {
            // Step 3
            // Save Test Information
            if (m_assemblyDataMap.isEmpty() == false)
            {
                log(" Updating Assembly Lot Information");
                AssemblyAdaptorDAO.saveAssemblyLot( null, m_assemblyDataMap.values().iterator(), WIP_TYPE_ASSEMBLY, VENDOR_NAME_SPEL );
            }
            else
            {
            	AssemblyAdaptorDAO.closeAssemblyLot(null, WIP_TYPE_ASSEMBLY, VENDOR_NAME_SPEL );
            }
            m_assemblyDataMap.clear();
            m_assemblyDataMap = null;
            success = true;
        }
        catch (ScalarException se)
        {
            success = false;
            se.printStackTrace();
            log("ERROR!! ERROR!! ERROR!! Could not update Assembly Lot information into database: " + se);
            throw se;
        }


        /*Updating the Test Lot Information*/
        try
        {
            // Step 3
            // Save Test Information
            if (m_testDataMap.isEmpty() == false)
            {
                log(" Updating Test Lot Information");
                TestAdaptorDAO.saveTestLot(null, m_testDataMap.values().iterator(), LOCATION_TYPE_FT, VENDOR_NAME_SPEL);
            }
            else
            {
                TestAdaptorDAO.closeTestLot(null, LOCATION_TYPE_FT, VENDOR_NAME_SPEL);
            }
            m_testDataMap.clear();
            m_testDataMap = null;
            success = true;
        }
        catch (ScalarException se)
        {
            success = false;
            se.printStackTrace();
            log("ERROR!! ERROR!! ERROR!! Could not update Test Lot information into database: " + se);
            throw se;
        }

        // to save the Tape & Reel lot info
        try
        {
           if ( m_trDataMap.isEmpty() == false )
           {
               log(" Updating Tape & Reel Lot Information");
               InventoryLotDAO.saveInventoryLot( null, m_trDataMap.values().iterator(), LOCATION_TYPE_TAPE_REEL, VENDOR_NAME_SPEL );
               success = true;
           }
           else
           {
               InventoryLotDAO.closeInventoryLot(null, LOCATION_TYPE_TAPE_REEL, VENDOR_NAME_SPEL );
           }
      }
      catch (ScalarException se)
      {
          success = false;
          se.printStackTrace();
          log("ERROR!! ERROR!! ERROR!! Could not update Finished Goods Lot information into database: " + se);
          throw se;
      }

        // to save the Finished Goods lot tables
       try
       {
           if ( m_fgDataMap.isEmpty() == false )
           {
               log(" Updating Finished Goods Lot Information");
               InventoryLotDAO.saveInventoryLot( null, m_fgDataMap.values().iterator(), LOCATION_TYPE_FG, VENDOR_NAME_SPEL );
               success = true;
           }
           else
           {
               InventoryLotDAO.closeInventoryLot(null, LOCATION_TYPE_FG, VENDOR_NAME_SPEL );
           }
      }
      catch (ScalarException se)
      {
          success = false;
          se.printStackTrace();
          log("ERROR!! ERROR!! ERROR!! Could not update Finished Goods Lot information into database: " + se);
          throw se;
      }

        // TO SEND THE NEW LOTS, NEW TRANSIT LOTS, NO PO LOTS, DISCREPANCY LOTS NOTIFICATIONS TO THE USER
        AdaptorDAO.sendAllNotifications(VENDOR_NAME_SPEL, true, true, true );

        // To Close the Drop Out Lots
        AdaptorDAO.closeDropOutLots(VENDOR_NAME_SPEL);

        int adaptor_id = AdaptorRunInfo.getLastRunAdaptorId( SPELPOP3Adaptor.ADAPTOR_NAME, CONSTANT_ADAPTOR);
        // Update Adaptor_Run_Info table
        try
        {
            if ( success == true )
            {
                AdaptorRunInfo.saveAdaptorLastUpdatedStatus(CONSTANT_PARSER, SPELPOP3Adaptor.ADAPTOR_NAME, CONSTANT_SUCCESS, adaptor_id);
                System.out.println( "SUCCESSFULLY UPDATED IN THE DATABASE" );
            }
        }
        catch ( ScalarException se )
        {
            success = false; //As filES DATA SAVED , UPDATEING AS SUCCESS
            AdaptorRunInfo.saveAdaptorLastUpdatedStatus(CONSTANT_PARSER, SPELPOP3Adaptor.ADAPTOR_NAME, CONSTANT_SUCCESS, adaptor_id);
            log( "WARNING!! WARNING!! WARNING!! Could not update Adaptor run information, Exception: " + se );
            throw se;
        }
        // Move the successful files to 'done' directory
       if ( success )
       {
           AdaptorUtils.moveFilesToDone( successFileList, doneDestDir );
       }
    }//end of execute method

    private void parseWipReport (String fileName ) throws Exception
    {
        if ( fileName == null ) throw new ScalarException( "Filename argument is null" );

        ParserResult result            = null;

        InventoryLot inventoryLot  = null;
        InventoryLot partailinventoryLot  = null;
        AssemblyLot  assemblyLot   = null;
        TestLot      testLot       = null;

        try
        {
            File detailWipFile = new File( fileName );
            if ( !detailWipFile.exists() )
            {
                throw new ScalarException( fileName + " doesnt exist" );
            }

            Parser parser = ParserFactory.getXLSParser( detailWipFile );
            log( "\nFound " + fileName );
            log( "\nParsing the file for Wip Report..." );
            System.out.println( "\nFound " + fileName );
            System.out.println( "\nParsing the file for Wip Report..." );

            ArrayList sheetNamesList = ((XLSParserImpl)parser).getSheetNamesList();

            for(int i= 0; i< sheetNamesList.size(); i++ )
            {
                String partNumber       = null;
                String fabLotNumber     = null;
                String lotNumber        = null;
                String lotCategory      = null;
                String allWafers = null;
                String sheetName = (String)sheetNamesList.get( i );
                //System.out.println("sheetName = " + sheetName);
                if ( sheetName != null && "Die Bank".equalsIgnoreCase(sheetName) )
                {
                    ((XLSParserImpl)parser).setHeaderRowIdx(5);
                    ((XLSParserImpl)parser).setDataStartRowIdx(6);

                    result = ((XLSParserImpl)parser).parse( sheetName );
					//System.out.println("headings" + result.getHeadings() );
                    while ( result.next() )
                    {
                        String waferIds = null;
                        if ( result.getString("Device Name") != null )
                        {
                            partNumber = result.getString("Device Name");
                        }    
                        lotNumber  = result.getString("Wafer lot #");

                        if ( partNumber != null && partNumber.trim().length() > 0 && lotNumber != null && lotNumber.trim().length() >0
                                && result.getString("Wafer number") != null && result.getString("Wafer number").trim().length() > 0
                                /*&& !"0".equalsIgnoreCase(result.getString("No of Wafer"))*/ )
                        {
							//System.out.println("result.getString" + result.getString("No of Wafer"));
                            waferIds =  AdaptorUtils.getWaferIDsByFormat(result.getString("Wafer number").replaceAll("\\(", "").replaceAll("\\)", "").replaceAll("to", "-").replaceAll(" ", "").replaceAll("[a-zA-Z]", "")) ;
							//System.out.println("waferIds" + waferIds);
                            inventoryLot = (InventoryLot)m_dieBankDataMap.get( partNumber + lotNumber );

                            if ( inventoryLot == null )                                                          
                            {
                                inventoryLot = new InventoryLot( VENDOR_NAME_SPEL, LOCATION_TYPE_DIE_BANK );
                                m_dieBankDataMap.put( partNumber + lotNumber, inventoryLot );
                            }

                            int quantity = 0;
                            int wquantity = 0;
                            if (inventoryLot.getQtyIn() != null)
                            {
                                try
                                {
                                    quantity = Integer.parseInt(inventoryLot.getQtyIn());
                                    wquantity = Integer.parseInt(inventoryLot.getWaferQtyIn());
                                }
                                catch (NumberFormatException ignore) {
                                }
								
                                wquantity += waferIds.split(";").length;
		
                                if ( result.getInt("Die qty") > 0 )
                                {
                                    quantity += result.getInt("Die qty");
                                }
                                else
                                {
                                    int dbProcessStepId = NewAdaptorDAO.getProcessStepId( partNumber, LOCATION_TYPE_DIE_BANK, LOCATION_TYPE_DIE_BANK );
                                    int gdpw = 1;
                                    try
                                    {   int fabProcessStepId = NewProductDAO.getForwardOrBackwardProcessstepId(null, dbProcessStepId,0, WIP_TYPE_FAB );
                                        gdpw = Integer.parseInt(NewProductDAO.getProcessParameterValue( null, fabProcessStepId, ParameterTypes.grossDieWafer ) ) ;
                                    }
                                    catch( NumberFormatException nfe ) { gdpw = 1; }
																	
                                    quantity +=  ( waferIds.split(";").length ) * gdpw;
							    }
                            }
                            else
                            {
                                wquantity = waferIds.split(";").length;
                                if ( result.getInt("Die qty") > 0 )
                                {
                                    quantity = result.getInt("Die qty");
                                }
                                else
                                {
                                    int dbProcessStepId = NewAdaptorDAO.getProcessStepId( partNumber, LOCATION_TYPE_DIE_BANK, LOCATION_TYPE_DIE_BANK );
                                    int gdpw = 1;
                                    try
                                    {
                                        int fabProcessStepId = NewProductDAO.getForwardOrBackwardProcessstepId(null, dbProcessStepId,0, WIP_TYPE_FAB );
                                        gdpw = Integer.parseInt(NewProductDAO.getProcessParameterValue( null, fabProcessStepId, ParameterTypes.grossDieWafer ) ) ;
                                    }
                                    catch( NumberFormatException nfe ) { gdpw = 1; }
									quantity = wquantity * gdpw;
								}
                            }
                            if ( inventoryLot.getWaferId() != null )
                            {
                                allWafers = inventoryLot.getWaferId() + waferIds;
                            }
                            else
                            {
                                allWafers = waferIds;
                            }

                            if (quantity > 0 )
                            {
                                inventoryLot.setPartNumber( partNumber );
                                inventoryLot.setLotNumber( lotNumber );
                                inventoryLot.setQtyIn(quantity + "");
                                inventoryLot.setWaferQtyIn( wquantity + "");
                                if (result.getDate("Receipt Date") != null )
                                {
                                    inventoryLot.setDateIn( StringUtils.formatDateToString( result.getDate("Receipt Date") ) );
                                }
                                inventoryLot.setFabLotNumber( lotNumber );
                                inventoryLot.setWaferId(allWafers);
                                inventoryLot.setStatus( LOT_STATUS_RUNNING );
                                inventoryLot.setLotCategory( LOT_CATEGORY_TYPE_PROD );
                            }
                        }
                        // For Partial Wafers
                        if ( partNumber != null && partNumber.trim().length() > 0 && lotNumber != null && lotNumber.trim().length() >0
                             && result.getString("Partial Wafer Qty") != null && result.getString("Partial Wafer Qty").trim().length() > 0 )
                        {
                            String partialwaferStr = result.getString("Partial Wafer Qty").replaceAll("#", "").replaceAll(" ", "").replaceAll("[a-zA-Z]", "");
                            String pwArry[] = partialwaferStr.split(",");
                            for (int pw = 0; pw < pwArry.length; pw++ )
                            {
                                String waferId = pwArry[pw].split("-")[0];
                                String binQty  = pwArry[pw].split("-")[1];

                                partailinventoryLot = (InventoryLot)m_partialWaferMap.get( partNumber + lotNumber + waferId );

                                if ( partailinventoryLot == null )
                                {
                                    partailinventoryLot = new InventoryLot( VENDOR_NAME_SPEL, LOCATION_TYPE_DIE_BANK );
                                    m_partialWaferMap.put( partNumber + lotNumber+ waferId, partailinventoryLot );
                                }
                                int qty = 0;
                                if (partailinventoryLot.getQtyIn() != null)
                                {
                                    try
                                    {
                                        qty = Integer.parseInt(partailinventoryLot.getQtyIn());
                                    }
                                    catch (NumberFormatException ignore) {
                                    }
                                    qty += Integer.parseInt(binQty);
                                }
                                else
                                {
                                    qty = Integer.parseInt(binQty);
                                }
                                if (qty > 0 )
                                {
                                    partailinventoryLot.setPartNumber( partNumber );
                                    partailinventoryLot.setLotNumber( lotNumber );
                                    partailinventoryLot.setQtyIn(qty + "");
                                    partailinventoryLot.setFabLotNumber( lotNumber );
                                    partailinventoryLot.setWaferId(waferId);
                                }
                            }//For loop
                        } // END OF Partial Wafers
                    }
                }//END OF DIE BANK

                //WIP
                if ( sheetName != null && ("WIP".equalsIgnoreCase(sheetName.toUpperCase()) || "ENGG".equalsIgnoreCase(sheetName.toUpperCase()) ) )
                {
                    if ("WIP".equalsIgnoreCase(sheetName.toUpperCase()) )
                    {
                        ((XLSParserImpl)parser).setHeaderRowIdx(5);
                        ((XLSParserImpl)parser).setDataStartRowIdx(6);
                    }
                    else
                    {
                        ((XLSParserImpl)parser).setHeaderRowIdx(2);
                        ((XLSParserImpl)parser).setDataStartRowIdx(3);
                    }

                     result = ((XLSParserImpl)parser).parse( sheetName );
					 //System.out.println("headings" + result.getHeadings() );
                     while ( result.next() )
                     {
                         partNumber = result.getString("DEVICE");
                         fabLotNumber = result.getString("WAFER LOT #");
                         lotNumber = result.getString("ASSLY LOT #");
                         if ( "PRODUCTION LOT".equalsIgnoreCase( partNumber ) )
                         {
                             lotCategory = LOT_CATEGORY_TYPE_PROD;
                         }
                         else if ("ENGINEERING LOT".equalsIgnoreCase( partNumber ) )
                         {
                             lotCategory = LOT_CATEGORY_TYPE_ENGG;
                         }

                         if ( partNumber != null && partNumber.trim().length() > 0 && lotNumber != null && lotNumber.trim().length() >0 )
                         {
                             if ( result.getInt("PO QUANTITY") > 0 && result.getInt("DS WIP") == 0  && result.getInt("DA - MD WIP") == 0  && result.getInt("PL - TF WIP") == 0 && result.getInt("ASSY OUTPUT") == 0
                                  && result.getInt("TEST WIP") == 0 && result.getInt("TEST OUTPUT") == 0  && result.getInt("TEST FALLOUT") == 0
                                  && result.getInt("VQC & T/R WIP") == 0 && result.getInt(25) == 0 && result.getInt(26) == 0 )
                             {
                                 assemblyLot = (AssemblyLot)m_assemblyDataMap.get( partNumber + lotNumber + fabLotNumber );

                                 if (assemblyLot == null)
                                 {
                                     assemblyLot = new AssemblyLot( VENDOR_NAME_SPEL, WIP_TYPE_ASSEMBLY );
                                     m_assemblyDataMap.put( partNumber + lotNumber + fabLotNumber, assemblyLot);
                                 }

                                 if( assemblyLot.getQtyIn() != null )
                                 {
                                    try
                                    {
                                        assemblyLot.setQtyIn( ( Integer.parseInt( assemblyLot.getQtyIn() ) + result.getInt("PO QUANTITY") ) + "" );
                                    }
                                    catch(Exception e)
                                    {
                                        assemblyLot.setQtyIn( result.getInt("PO QUANTITY")+ "");
                                    }
                                 }
                                 else
                                 {
                                    assemblyLot.setQtyIn( result.getInt("PO QUANTITY") +"" );
                                 }
                                 if ( assemblyLot.getStage() != null && !"ASSY QUEUE".equalsIgnoreCase( assemblyLot.getStage() ) )
                                 {
                                    assemblyLot.setStage ( assemblyLot.getStage() +", ASSY QUEUE") ;
                                 }
                                 else
                                 {
                                    assemblyLot.setStage("ASSY QUEUE");
                                 }
                                 if ( assemblyLot.getQtyIn() != null && assemblyLot.getQtyIn().equalsIgnoreCase("0") == false )
                                 {
                                     assemblyLot.setLotNumber( lotNumber );
                                     assemblyLot.setDeviceName( partNumber );
                                     assemblyLot.setStatus( LOT_STATUS_RUNNING );
                                     assemblyLot.setFabLotNumber( fabLotNumber );
                                     assemblyLot.setPackageType( result.getString("PKG /LD") );
                                     assemblyLot.setPONumber( result.getString("PO #") );
                                     assemblyLot.setDateCode( result.getString("DATE CODE") );
                                     assemblyLot.setCategory( lotCategory );
                                     if (result.getDate("ASSY START DATE") != null )
                                     {
                                         assemblyLot.setDateIn(StringUtils.formatDateToString(result.getDate("ASSY START DATE")));
                                     }
                                     else if (result.getDate("DIE RECD. DATE") != null )
                                     {
                                         assemblyLot.setDateIn(StringUtils.formatDateToString(result.getDate("DIE RECD. DATE")));
                                     }
                                     if (result.getDate("ASSY Comp. DATE") != null )
                                     {
                                         assemblyLot.setExpectedDateOut(StringUtils.formatDateToString(result.getDate("ASSY Comp. DATE")));
                                     }

                                     assemblyLot.setProcessType( WIP_TYPE_ASSEMBLY );
                                 }

                             } // ASSY PO QUANTITY

                             if (result.getInt("DS WIP") > 0  || result.getInt("DA - MD WIP") > 0  || result.getInt("PL - TF WIP") > 0 )
                             {
                                 assemblyLot = (AssemblyLot)m_assemblyDataMap.get( partNumber + lotNumber + fabLotNumber );

                                 if (assemblyLot == null)
                                 {
                                     assemblyLot = new AssemblyLot( VENDOR_NAME_SPEL, WIP_TYPE_ASSEMBLY );
                                     m_assemblyDataMap.put( partNumber + lotNumber + fabLotNumber, assemblyLot);
                                 }

                                 // FOR SETTING THE QTY IN
                                 if( result.getInt("DS WIP") > 0 )
                                 {
                                    assemblyLot = (AssemblyLot) m_assemblyDataMap.get(partNumber + lotNumber + fabLotNumber);

                                    if( assemblyLot.getQtyIn() != null )
                                    {
                                        try
                                        {
                                            assemblyLot.setQtyIn( ( Integer.parseInt( assemblyLot.getQtyIn() ) + result.getInt("DS WIP") ) + "" );
                                        }
                                        catch(Exception e)
                                        {
                                            assemblyLot.setQtyIn( result.getInt("DS WIP")+ "");
                                        }
                                    }
                                    else
                                    {
                                        assemblyLot.setQtyIn( result.getInt("DS WIP") +"" );
                                    }
                                    if ( assemblyLot.getStage() != null && !"Die Store".equalsIgnoreCase( assemblyLot.getStage() ) )
                                    {
                                        assemblyLot.setStage ( assemblyLot.getStage() +", Die Store") ;
                                    }
                                    else
                                    {
                                        assemblyLot.setStage("Die Store");
                                    }
                                }

                                if( result.getInt("DA - MD WIP") > 0 )
                                {
                                    assemblyLot = (AssemblyLot) m_assemblyDataMap.get(partNumber + lotNumber + fabLotNumber);

                                    if( assemblyLot.getQtyIn() != null )
                                    {
                                        try
                                        {
                                            assemblyLot.setQtyIn( ( Integer.parseInt( assemblyLot.getQtyIn() ) + result.getInt("DA - MD WIP") ) + "" );
                                        }
                                        catch(Exception e)
                                        {
                                            assemblyLot.setQtyIn( result.getInt("DA - MD WIP")+ "");
                                        }
                                    }
                                    else
                                    {
                                        assemblyLot.setQtyIn( result.getInt("DA - MD WIP") +"" );
                                    }
                                    if ( assemblyLot.getStage() != null && !"Die Attach-Molding".equalsIgnoreCase( assemblyLot.getStage() ) )
                                    {
                                        assemblyLot.setStage ( assemblyLot.getStage() +", Die Attach-Molding") ;
                                    }
                                    else
                                    {
                                        assemblyLot.setStage("Die Attach-Molding");
                                    }
                                }

                                if( result.getInt("PL - TF WIP") > 0 )
                                {
                                    assemblyLot = (AssemblyLot) m_assemblyDataMap.get(partNumber + lotNumber + fabLotNumber);

                                    if( assemblyLot.getQtyIn() != null )
                                    {
                                        try
                                        {
                                            assemblyLot.setQtyIn( ( Integer.parseInt( assemblyLot.getQtyIn() ) + result.getInt("PL - TF WIP") ) + "" );
                                        }
                                        catch(Exception e)
                                        {
                                            assemblyLot.setQtyIn( result.getInt("PL - TF WIP")+ "");
                                        }
                                    }
                                    else
                                    {
                                        assemblyLot.setQtyIn( result.getInt("PL - TF WIP") +"" );
                                    }
                                    if ( assemblyLot.getStage() != null && !"Plating-TF".equalsIgnoreCase( assemblyLot.getStage() ) )
                                    {
                                        assemblyLot.setStage ( assemblyLot.getStage() +", Plating-TF") ;
                                    }
                                    else
                                    {
                                        assemblyLot.setStage("Plating-TF");
                                    }
                                }

                                if ( assemblyLot.getQtyIn() != null && assemblyLot.getQtyIn().equalsIgnoreCase("0") == false )
                                {
                                    assemblyLot.setLotNumber( lotNumber );
                                    assemblyLot.setDeviceName( partNumber );
                                    assemblyLot.setStatus( LOT_STATUS_RUNNING );
                                    assemblyLot.setFabLotNumber( fabLotNumber );
                                    assemblyLot.setPackageType( result.getString("PKG /LD") );
                                    assemblyLot.setPONumber( result.getString("PO #") );
                                    assemblyLot.setDateCode( result.getString("DATE CODE") );
                                    assemblyLot.setCategory( lotCategory );
                                    if (result.getDate("ASSY START DATE") != null )
                                    {
                                        assemblyLot.setDateIn(StringUtils.formatDateToString(result.getDate("ASSY START DATE")));
                                    }
                                    else if (result.getDate("DIE RECD. DATE") != null )
                                    {
                                        assemblyLot.setDateIn(StringUtils.formatDateToString(result.getDate("DIE RECD. DATE")));
                                    }
                                    if (result.getDate("ASSY Comp. DATE") != null )
                                    {
                                        assemblyLot.setExpectedDateOut(StringUtils.formatDateToString(result.getDate("ASSY Comp. DATE")));
                                    }

                                    assemblyLot.setProcessType( WIP_TYPE_ASSEMBLY );
                                }

                             } //END OF ASSY WIP
                             
                             //TEST WIP
                             if ( result.getInt("TEST WIP") > 0 || result.getInt("TEST OUTPUT") > 0  || result.getInt("TEST FALLOUT") > 0 )
                             {
                                 testLot = (TestLot)m_testDataMap.get( partNumber + lotNumber + fabLotNumber );

                                 if ( testLot == null )
                                 {
                                     testLot = new TestLot( VENDOR_NAME_SPEL, LOCATION_TYPE_FT );
                                     m_testDataMap.put( partNumber + lotNumber + fabLotNumber, testLot );
                                 }
								 if ( result.getInt("TEST WIP") > 0 )
								 {
									 testLot = (TestLot)m_testDataMap.get( partNumber + lotNumber + fabLotNumber );

                                    if( testLot.getQtyIn() != null )
                                    {
                                        try
                                        {
                                            testLot.setQtyIn( ( Integer.parseInt( testLot.getQtyIn() ) + result.getInt("TEST WIP") ) + "" );
                                        }
                                        catch(Exception e)
                                        {
                                            testLot.setQtyIn( result.getInt("TEST WIP")+ "");
                                        }
                                    }
                                    else
                                    {
                                        testLot.setQtyIn( result.getInt("TEST WIP") +"" );
                                    }
                                    if ( testLot.getStage() != null && !"TEST WIP".equalsIgnoreCase( testLot.getStage() ) )
                                    {
                                        testLot.setStage ( testLot.getStage() +", TEST WIP") ;
                                    }
                                    else
                                    {
                                        testLot.setStage("TEST WIP");
                                    }
								 }
								 /*if ( result.getInt("TEST OUTPUT") > 0 )
								 {
									 testLot = (TestLot)m_testDataMap.get( partNumber + lotNumber + fabLotNumber );

                                    if( testLot.getQtyIn() != null )
                                    {
                                        try
                                        {
                                            testLot.setQtyIn( ( Integer.parseInt( testLot.getQtyIn() ) + result.getInt("TEST OUTPUT") ) + "" );
                                        }
                                        catch(Exception e)
                                        {
                                            testLot.setQtyIn( result.getInt("TEST OUTPUT")+ "");
                                        }
                                    }
                                    else
                                    {
                                        testLot.setQtyIn( result.getInt("TEST OUTPUT") +"" );
                                    }
                                    if ( testLot.getStage() != null && !"TEST OUTPUT Bin1".equalsIgnoreCase( testLot.getStage() ) )
                                    {
                                        testLot.setStage ( testLot.getStage() +", TEST OUTPUT Bin1") ;
                                    }
                                    else
                                    {
                                        testLot.setStage("TEST OUTPUT Bin1");
                                    }
								 }
								 if ( result.getInt("TEST FALLOUT") > 0 )
								 {
									 testLot = (TestLot)m_testDataMap.get( partNumber + lotNumber + fabLotNumber );

                                    if( testLot.getQtyIn() != null )
                                    {
                                        try
                                        {
                                            testLot.setQtyIn( ( Integer.parseInt( testLot.getQtyIn() ) + result.getInt("TEST FALLOUT") ) + "" );
                                        }
                                        catch(Exception e)
                                        {
                                            testLot.setQtyIn( result.getInt("TEST FALLOUT")+ "");
                                        }
                                    }
                                    else
                                    {
                                        testLot.setQtyIn( result.getInt("TEST FALLOUT") +"" );
                                    }
                                    if ( testLot.getStage() != null && !"TEST FALLOUT".equalsIgnoreCase( testLot.getStage() ) )
                                    {
                                        testLot.setStage ( testLot.getStage() +", TEST FALLOUT") ;
                                    }
                                    else
                                    {
                                        testLot.setStage("TEST FALLOUT");
                                    }
								 }*/

                                 if ( testLot.getQtyIn() != null && !"0".equalsIgnoreCase(testLot.getQtyIn()) )
                                 {
                                    testLot.setPartNumber( partNumber );
                                    testLot.setLotNumber( lotNumber);

                                    //testLot.setQtyIn( quantity +"");
                                    testLot.setFabLotNumber( fabLotNumber );
                                    testLot.setDateCode( result.getString("DATE CODE") );
                                    testLot.setPackageType(result.getString("PKG /LD"));
                                    testLot.setPoNumber(result.getString("PO #"));
                                    testLot.setStatus( LOT_STATUS_RUNNING );
                                    testLot.setCategory( lotCategory );
                                }

                             }
                             //T&R WIP
                             if ( result.getInt("VQC & T/R WIP") > 0 )
                             {
                                 inventoryLot = m_trDataMap.get( partNumber + lotNumber + fabLotNumber );

                                if( inventoryLot == null )
                                {
                                    inventoryLot = new InventoryLot( VENDOR_NAME_SPEL,  LOCATION_TYPE_TAPE_REEL);
                                    m_trDataMap.put(  partNumber + lotNumber + fabLotNumber , inventoryLot );
                                }
                                int quantity = 0;
                                if (inventoryLot.getQtyIn() != null )
                                {
                                    try
                                    {
                                        quantity = Integer.parseInt(inventoryLot.getQtyIn() );
                                    }
                                    catch (NumberFormatException nfe)
                                    {
                                        quantity = 0;
                                    }
                                    quantity += result.getInt("VQC & T/R WIP");
                                }
                                else
                                {
                                    quantity = result.getInt("VQC & T/R WIP");
                                }
                                if( quantity > 0)
                                {
                                    inventoryLot.setPartNumber( partNumber );
                                    inventoryLot.setLotNumber( lotNumber );
                                    inventoryLot.setFabLotNumber( fabLotNumber );
                                    inventoryLot.setQtyIn( quantity+"" );
                                    if (result.getDate("ACT. FG DATE") != null )
                                    {
                                        inventoryLot.setDateIn(StringUtils.formatDateToString(result.getDate("ACT. FG DATE")));
                                    }
                                    inventoryLot.setStatus(LOT_STATUS_RUNNING);
                                    inventoryLot.setLotCategory( lotCategory );
                                    inventoryLot.setDateCode( result.getString("DATE CODE") );
                                    inventoryLot.setPkgType( result.getString("PKG /LD") );
                                    inventoryLot.setCustomerPO(result.getString("PO #"));
                                }

                             }
                             //FG
                             if ( result.getInt(25) > 0 || result.getInt(26) > 0 )
                             {
                                log("IN FG lotNumber = " + fabLotNumber);
                                inventoryLot = m_fgDataMap.get( partNumber + lotNumber + fabLotNumber );

                                if( inventoryLot == null )
                                {
                                    inventoryLot = new InventoryLot( VENDOR_NAME_SPEL,  LOCATION_TYPE_FG);
                                    m_fgDataMap.put(  partNumber + lotNumber + fabLotNumber , inventoryLot );
                                }
                                if (result.getInt(25) > 0 )
                                {
									inventoryLot = m_fgDataMap.get( partNumber + lotNumber + fabLotNumber );

                                    if( inventoryLot.getQtyIn() != null )
                                    {
                                        try
                                        {
                                            inventoryLot.setQtyIn( ( Integer.parseInt( inventoryLot.getQtyIn() ) + result.getInt(25) ) + "" );
                                        }
                                        catch(Exception e)
                                        {
                                            inventoryLot.setQtyIn( result.getInt(25)+ "");
                                        }
                                    }
                                    else
                                    {
                                        inventoryLot.setQtyIn( result.getInt(25) +"" );
                                    }
                                    if ( inventoryLot.getStage() != null && !"FG REEL".equalsIgnoreCase( inventoryLot.getStage() ) )
                                    {
                                        inventoryLot.setStage ( inventoryLot.getStage() +", FG REEL") ;
                                    }
                                    else
                                    {
                                        inventoryLot.setStage("FG REEL");
                                    }
                                }
								if (result.getInt(26) > 0 )
                                {
									//System.out.println("result.getInt(24)" + result.getInt(24) );
									inventoryLot = m_fgDataMap.get( partNumber + lotNumber + fabLotNumber );

                                    if( inventoryLot.getQtyIn() != null )
                                    {
                                        try
                                        {
                                            inventoryLot.setQtyIn( ( Integer.parseInt( inventoryLot.getQtyIn() ) + result.getInt(26) ) + "" );
                                        }
                                        catch(Exception e)
                                        {
                                            inventoryLot.setQtyIn( result.getInt(26)+ "");
                                        }
                                    }
                                    else
                                    {
                                        inventoryLot.setQtyIn( result.getInt(26) +"" );
                                    }
                                    if ( inventoryLot.getStage() != null && !"FG TUBE".equalsIgnoreCase( inventoryLot.getStage() ) )
                                    {
                                        inventoryLot.setStage ( inventoryLot.getStage() +", FG TUBE") ;
                                    }
                                    else
                                    {
                                        inventoryLot.setStage("FG TUBE");
                                    }
                                }
                                if( inventoryLot.getQtyIn() != null &&  !"0".equalsIgnoreCase(inventoryLot.getQtyIn()) )
                                {
                                    inventoryLot.setPartNumber( partNumber );
                                    inventoryLot.setLotNumber( lotNumber );
                                    inventoryLot.setFabLotNumber( fabLotNumber );
                                    //inventoryLot.setQtyIn( quantity+"" );
                                    if (result.getDate("ACT. FG DATE") != null )
                                    {
                                        inventoryLot.setDateIn(StringUtils.formatDateToString(result.getDate("ACT. FG DATE")));
                                    }
                                    inventoryLot.setStatus(LOT_STATUS_RUNNING);
                                    inventoryLot.setLotCategory( lotCategory );
                                    inventoryLot.setDateCode( result.getString("DATE CODE") );
                                    inventoryLot.setPkgType( result.getString("PKG /LD") );
                                    inventoryLot.setCustomerPO(result.getString("PO #"));
                                }
                            } //END OF FG
                        }
                    } //END OF WHILE 
                }//END OF WIP sheet
            }
        }
        catch(Exception e )
        {
            e.printStackTrace();
            System.out.println("!ERROR !ERROR While parsing the SPEL WIP sheets");
            throw e;
        }
    }

    private void log(String message)
    {
        if (message == null) return;

        if (m_parserLogger != null)
        {
            synchronized (m_parserLogger)
            {
                m_parserLogger.logDebug(message + LINE_SEPARATOR);
            }
        }
        else
        {
            System.out.println(message);
        }
    }

}

